﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace VIT.Provider
{
    public class Product : Product_Base
    {
        #region Variable
        string user = HttpContext.Current.Session["userName"].ToString();
        #endregion

        #region Constructor
        public Product(int id = -1,
                        int categoryId = 0,
                        int manufacturerId = 0,
                        int supplierId = 0,
            int Color = 0,
            int Style = 0,
                        decimal price = 0,
                        decimal sale = 0,
                        int views = 0,
                        string languageId = "",
                        string image = "",
                        string title = "", 
                        string brief = "",
                        string description = "",
                        string tag = "",
                        string type="",
                        int orders = 0,
                        bool published = true)
        {
            this.Id = id;
            this.CategoryId = categoryId;
            this.ManufacturerId = manufacturerId;
            this.SupplierId = supplierId;
            this.LanguageId = languageId;
            this.Type = type;
            this.Image = image;
            this.Title = title;
            this.Brief = brief;
            this.Description = description;
            this.Price = price;
            this.Sale = sale;
            this.Views = views;
            this.Tag = tag;
            this.Orders = orders;
            this.Published = published;
        }
        #endregion

        #region Lists
        public DataTable Filter(string SortColumn = "", string manufacturerId = "0", string supplierId = "0", string categoryList = "", bool desc = true, bool isTransaction = false)
        {
            string sort = (desc) ? " DESC" : " ";
            string strQuery = "select Id, CategoryId, Color, Style, Title, Brief, Description, Price, Sale, Image, Orders, Tag, Views, Published from Products a join Products_Language l on Id=ProductId and LanguageID=@LanguageID where Deleted=0 ";
            if (SortColumn == "") SortColumn = "Orders";
            if (categoryList != "") strQuery += "and CategoryId in (" + categoryList + ") ";
            if (manufacturerId != "0") strQuery += "and ManufacturerId = @ManufacturerId ";
            if (supplierId != "0") strQuery += "and SupplierId = @SupplierId ";
            strQuery += "order by " + SortColumn + sort;

            SqlParameter LanguageId = new SqlParameter("@LanguageID", this.LanguageId);
            SqlParameter ManufacturerId = new SqlParameter("@ManufacturerId", manufacturerId);
            SqlParameter SupplierId = new SqlParameter("@SupplierId", supplierId);
            return ExecuteDataset(isTransaction, strQuery, LanguageId, ManufacturerId, SupplierId).Tables[0];
        }

        public bool Delete(int delete, int id, bool isTransaction = false)
        {
            string strQuery = "update Products set Published=@Published, Deleted=@Deleted ,ModifyDate=@Now, ModifyByUser=@User, DeleteByUser=@DeleteByUser where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", (delete == 0) ? 1 : 0);
            SqlParameter D = new SqlParameter("@Deleted", delete);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter DeleteByUser = new SqlParameter("@DeleteByUser", (delete == 1) ? user : "");
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Id, P, D, User, Now, DeleteByUser) > 0;
        }

        public bool Publish(int publish, int id, bool isTransaction = false)
        {
            string strQuery = "update Products set Published=@Published, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter P = new SqlParameter("@Published", publish);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Id, P, Now, User) > 0;
        }

        public bool Order(string order, int id, bool isTransaction = false)
        {
            string strQuery = "update Products set Orders=Orders " + order + " 1, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, strQuery, Id, Now, User) > 0;
        }
        #endregion

        #region Edit
        public void GetSingle(bool isTransaction = false)
        {
            if (Exist(isTransaction))
            {
                string strQuery = "select CategoryId, Title, ManufacturerId, SupplierId, Color, Style, Image, Brief, Description, Price, Sale, Views, Tag, Type, Orders, Published from Products join Products_Language on Id=ProductId and LanguageID=@LanguageId where Id=@Id";
                SqlParameter Id = new SqlParameter("@Id", this.Id);
                SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
                DataTable dt = ExecuteDataset(isTransaction, strQuery, Id, LanguageId).Tables[0];

                if (dt.Rows.Count > 0)
                {
                    this.Image = dt.Rows[0]["Image"].ToString();
                    if (!string.IsNullOrEmpty(dt.Rows[0]["Orders"].ToString()))
                        this.Orders = int.Parse(dt.Rows[0]["Orders"].ToString());
                    this.Title = dt.Rows[0]["Title"].ToString();
                    this.Brief = dt.Rows[0]["Brief"].ToString();
                    this.Description = dt.Rows[0]["Description"].ToString();

                    decimal price, sale;
                    int views, manufacturerId, supplierId;
                    decimal.TryParse(dt.Rows[0]["Price"].ToString(), out price);
                    decimal.TryParse(dt.Rows[0]["Sale"].ToString(), out sale);
                    int.TryParse(dt.Rows[0]["Views"].ToString(), out views);
                    int.TryParse(dt.Rows[0]["ManufacturerId"].ToString(), out manufacturerId);
                    int.TryParse(dt.Rows[0]["SupplierId"].ToString(), out supplierId);

                    this.Price = price; this.Sale = sale;
                    this.Views = views; this.ManufacturerId = manufacturerId; this.SupplierId = supplierId;

                    this.Color = dt.Rows[0]["Color"].ToString();
                    this.Style = dt.Rows[0]["Style"].ToString();
                    this.Tag = dt.Rows[0]["Tag"].ToString();
                    this.Type = dt.Rows[0]["Type"].ToString();
                    this.CategoryId = Convert.ToInt32(dt.Rows[0]["CategoryId"]);
                    if(!string.IsNullOrEmpty(dt.Rows[0]["Published"].ToString()))
                        this.Published = (bool)dt.Rows[0]["Published"];
                }
            }
            else this.Id = 0;
        }

        public bool hasType(string type, bool isTransaction = false)
        {
            string strQuery = "select Id from Products where Type like '%" + type + "%' and Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(isTransaction, strQuery, Id) != null;
        }

        public bool Exist(bool isTransaction = false)
        {
            string strQuery = "select Id from Products where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            return ExecuteScalar(isTransaction, strQuery, Id) != null;
        }

        public bool Exist(int id, string languageid, bool isTransaction = false)
        {
            string strQuery = "select ProductId from Products_Language where ProductId=@Id and LanguageID=@LanguageId";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", languageid);
            return ExecuteScalar(isTransaction, strQuery, Id, LanguageId) != null;
        }

        public bool Save(bool isTransaction = true)
        {
            TransactionBegin();
            try
            {
                if (Exist(isTransaction)) // sửa
                {
                    Delete(0, this.Id, isTransaction);
                    UpdateObj(isTransaction);

                    if (Exist(this.Id, this.LanguageId, isTransaction)) UpdateObjLang(isTransaction);
                    else InsertObjLang(isTransaction);
                }
                else // thêm
                {
                    this.Id = InsertObj(isTransaction);
                    InsertObjLang(isTransaction);
                    string sql = string.Format("update Product_Image SET ProductId = {0} WHERE ProductId = {1}", this.Id,
                        Convert.ToString(HttpContext.Current.Session["sId_ProductImage"]));
                    ExecuteNonQuery(isTransaction, sql);
                }

                TransactionCommit();
                return true;
            }
            catch
            {
                TransactionRollback();
                return false;
            }
        }

        public int InsertObj(bool isTransaction = false)
        {
            string sqlCat = "insert into Products(CategoryId, ManufacturerId, SupplierId, Color, Style, Image, Price, Sale, Views, Tag, Type, Orders, Published, Deleted, CreateDate, ModifyDate, CreateByUser, ModifyByUser)"
                            + "values (@CategoryId, @ManufacturerId, @SupplierId, @Color, @Style, @Image, @Price, @Sale, @Views, @Tag, @Type, @Orders, @Published, 0, @Now, @Now, @User, @User)";

            SqlParameter CategoryId = new SqlParameter("@CategoryId", this.CategoryId);
            SqlParameter ManufacturerId = new SqlParameter("@ManufacturerId", this.ManufacturerId);
            SqlParameter SupplierId = new SqlParameter("@SupplierId", this.SupplierId);
            SqlParameter Color = new SqlParameter("@Color", this.Color);
            SqlParameter Style = new SqlParameter("@Style", this.Style);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter Price = new SqlParameter("@Price", this.Price);
            SqlParameter Sale = new SqlParameter("@Sale", this.Sale);
            SqlParameter Views = new SqlParameter("@Views", this.Views);
            SqlParameter Tag = new SqlParameter("@Tag", this.Tag);
            SqlParameter Type = new SqlParameter("@Type", this.Type);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlCat, CategoryId, ManufacturerId, SupplierId, Color, Style, Now, Image, Price, Sale, Views, Tag, Type, Orders, Published, User);
        }

        public int InsertObjLang(bool isTransaction = false)
        {
            string sqlLang = "insert into Products_Language (ProductId, LanguageId, Title, Brief, Description, CreateDate, ModifyDate, CreateByUser, ModifyByUser)"
                                                + "values (@ProductId, @LanguageId, @Title, @Brief, @Description, @Now, @Now,@User, @User)";
            SqlParameter ArticleId = new SqlParameter("@ProductId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Brief = new SqlParameter("@Brief", this.Brief);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);
            return ExecuteNonQuery(isTransaction, sqlLang, ArticleId, LanguageId, Title, Brief, Description, Now, User);
        }

        public bool UpdateObj(bool isTransaction = false)
        {
            string sqlCat = "update Products set CategoryId=@CategoryId, ManufacturerId=@ManufacturerId, SupplierId=@SupplierId, Color=@Color, Style=@Style, Image=@Image, Price=@Price, Sale=@Sale, Views=@Views, Tag=@Tag, Type=@Type, Orders=@Orders, Published=@Published, ModifyDate=@Now, ModifyByUser=@User where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", this.Id);
            SqlParameter CategoryId = new SqlParameter("@CategoryId", this.CategoryId);
            SqlParameter ManufacturerId = new SqlParameter("@ManufacturerId", this.ManufacturerId);
            SqlParameter SupplierId = new SqlParameter("@SupplierId", this.SupplierId);
            SqlParameter Color = new SqlParameter("@Color", this.Color);
            SqlParameter Style = new SqlParameter("@Style", this.Style);
            SqlParameter Image = new SqlParameter("@Image", this.Image);
            SqlParameter Price = new SqlParameter("@Price", this.Price);
            SqlParameter Sale = new SqlParameter("@Sale", this.Sale);
            SqlParameter Views = new SqlParameter("@Views", this.Views);
            SqlParameter Tag = new SqlParameter("@Tag", this.Tag);
            SqlParameter Type = new SqlParameter("@Type", this.Type);
            SqlParameter Orders = new SqlParameter("@Orders", this.Orders);
            SqlParameter Published = new SqlParameter("@Published", this.Published);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlCat, Id, CategoryId, ManufacturerId, SupplierId, Color, Style, Image, Price, Sale, Views, Tag, Type, Orders, Published, Now, User) > 0;
        }

        public int UpdateObjLang(bool isTransaction = false)
        {
            string sqlLang = "update Products_Language set Title=@Title, Brief=@Brief, Description=@Description, ModifyDate=@Now, ModifyByUser=@User where ProductId=@ProductId and LanguageId=@LanguageId";
            SqlParameter ArticleId = new SqlParameter("@ProductId", this.Id);
            SqlParameter LanguageId = new SqlParameter("@LanguageId", this.LanguageId);
            SqlParameter Title = new SqlParameter("@Title", this.Title);
            SqlParameter Brief = new SqlParameter("@Brief", this.Brief);
            SqlParameter Description = new SqlParameter("@Description", this.Description);
            SqlParameter User = new SqlParameter("@User", user);
            SqlParameter Now = new SqlParameter("@Now", DateTime.Now);

            return ExecuteNonQuery(isTransaction, sqlLang, ArticleId, LanguageId, Title, Brief, Description, Now, User);
        }
        #endregion

        #region Trash
        public bool CategoryIdExist(int delete, string id, bool isTransaction = false)
        {
            string strQuery = "select c.Id from Products p, Category c where p.Id=@Id and c.Deleted=@Delete and p.CategoryId=c.Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            SqlParameter d = new SqlParameter("@Delete", delete);
            object obj = ExecuteScalar(isTransaction, strQuery, Id, d);
            return (obj != null) ? true : false;
        }

        public DataTable GetRecycleBin(string SortColumn, bool isTransaction = false)
        {
            string strQuery;
            strQuery = "select Id, Price, Sale, Image, Orders, Tag, Views, CategoryId, ManufacturerId, SupplierId, Price, Sale, Orders, Tag, Views, CreateDate, ModifyDate, CreateByUser, ModifyByUser, DeleteByUser from Products where Deleted=1";
            if(SortColumn!="") strQuery += " Order By " + SortColumn;
            return ExecuteDataset(isTransaction, strQuery).Tables[0];
        }

        public bool Cleared(string id, bool isTransaction = false)
        {
            string strQuery = "delete from Products where Id=@Id";
            SqlParameter Id = new SqlParameter("@Id", id);
            return ExecuteNonQuery(isTransaction, strQuery, Id) > 0;
        }
        #endregion
    }
}
